<?php
// Tim'Roster
// Copyright (c) 2011 Nicolas Nallet <aspgic2@gmail.com>
//
// This file is part of Tim'Roster.
//
// Tim'Roster is free software; you can redistribute it and/or modify
// it under the terms of the GNU Affero General Public License as published by
// the Free Software Foundation; either version 3 of the License, or
// (at your option) any later version.
//
// Tim'Roster is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Affero General Public License for more details.
//
// You should have received a copy of the GNU Affero General Public License
// along with this program; if not, write to the Free Software Foundation,
// Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301  USA

$xml_config = simplexml_load_file('./config.xml');

include_once($_SERVER["DOCUMENT_ROOT"].$xml_config->roster_root_directory.'/data_access/base_DA.php');

class user_DA extends base_DA {

    /** User Fields Name **/
    CONST FL_USR_ID = 'id';
    CONST FL_USR_USER = 'user';
    CONST FL_USR_PASSWORD = 'password';
    CONST FL_USR_PW_EXPIRED = 'pw_expired';
    CONST FL_USR_EMAIL = 'email';
    CONST FL_USR_CREATION_DATE = 'creation_date';
    CONST FL_USR_LOGIN_FAILED_COUNT = 'login_failed_count';
    CONST FL_USR_LAST_LOGIN_FAILED_DATE = 'last_login_failed_date';
    CONST FL_USR_ACTIVE = 'active';
    CONST FL_USR_ACTICATION_TOKEN = 'activation_token';
    CONST FL_USR_DEBAN_DATE = 'deban_date';
    CONST FL_USR_GROUP_ID = 'group_id';
    CONST FL_USR_LAST_LOGIN = 'last_login';


    /** User Group Fields Name **/


    CONST FL_GRP_ID = 'id';
    CONST FL_GRP_LABEL = 'label';

    /** User Token Fields Name **/

    CONST FL_TOK_USER_ID = 'user_id';
    CONST FL_TOK_TOKEN = 'token';
    CONST FL_TOK_TOKEN_EXPIRED = 'token_expired';

    /**********************/


    /**
     * (PHP 5)<br/>
     * Check if user exists in DB
     * @param string $username
     * @return bool
     */
    public function user_exists($username){
        $sql_cmd = 'SELECT '.user_DA::FL_USR_USER.' FROM '.$this->get_table_prefix().'_user WHERE '.user_DA::FL_USR_USER.' = '.$this->format_string_to_sql_param($username);
//        echo '<br>user_DA->user_exists : '.$sql_cmd;
        $result = $this->execute_sql_command($sql_cmd, false);
        return !empty($result);
    }

    /**
     * (PHP 5)<br/>
     * Check if email exists in DB
     * @param string $email
     * @return bool
     */
    public function email_exists($email){
        $sql_cmd = 'SELECT '.user_DA::FL_USR_EMAIL.' FROM '.$this->get_table_prefix().'_user WHERE '.user_DA::FL_USR_EMAIL.' = '.$this->format_string_to_sql_param($email);
//        echo '<br>user_DA->email_exists : '.$sql_cmd;
        $result = $this->execute_sql_command($sql_cmd, false);
        return !empty($result);
    }

    /**
     * (PHP 5)<br/>
     * Register user in DB
     * @param string $username
     * @param string $password
     * @param string $email
     * @param string $activation_token
     */
    public function register_user($username, $password, $email, $activation_token){
        $sql_cmd = 'INSERT INTO '.$this->get_table_prefix().'_user ('.user_DA::FL_USR_USER.', '.user_DA::FL_USR_PASSWORD.', '.user_DA::FL_USR_PW_EXPIRED.', '
                           .user_DA::FL_USR_EMAIL.', '.user_DA::FL_USR_CREATION_DATE.', '.user_DA::FL_USR_ACTICATION_TOKEN.') values ('
                           .$this->format_string_to_sql_param($username).', '
                           .$this->format_string_to_sql_param($password).', 0, '
                           .$this->format_string_to_sql_param($email).', NOW(), '
                           .$this->format_string_to_sql_param($activation_token).')';

//        echo '<br>user_DA->register_user : '.$sql_cmd;

        return $this->execute_sql_command($sql_cmd, true);
    }

    /**
     * (PHP 5)<br/>
     * Active an account
     * @param int $user_id
     */
    public function user_activation($user_id){
        $sql_cmd = 'UPDATE '.$this->get_table_prefix().'_user SET '.user_DA::FL_USR_ACTIVE.' = 1, '.user_DA::FL_USR_ACTICATION_TOKEN.' = null WHERE '.user_DA::FL_USR_ID.' = '.$user_id;
//        echo '<br>user_DA->user_activation : '.$sql_cmd;
        $this->execute_sql_command($sql_cmd, true);
    }

    /**
     * (PHP 5)<br/>
     * Get the user password
     * @param string $username
     * @return string
     */
    public function get_user_password($username){
        $sql_cmd = 'SELECT '.user_DA::FL_USR_ID.', '.user_DA::FL_USR_USER.', '.user_DA::FL_USR_PASSWORD.', '
                        .user_DA::FL_USR_PW_EXPIRED.' FROM '.$this->get_table_prefix().'_user WHERE '
                        .user_DA::FL_USR_USER.' = '.$this->format_string_to_sql_param($username);
//        echo '<br>user_DA->get_user_password : '.$sql_cmd;
        $result = $this->execute_sql_command($sql_cmd, false);
        return $result;
    }

    /**
     * (PHP 5)<br/>
     * Update last_login field in DB
     * @param string $username
     */
    public function update_user_last_login($username){
        $sql_cmd = 'UPDATE '.$this->get_table_prefix().'_user SET '.user_DA::FL_USR_LAST_LOGIN.' = NOW() WHERE '.user_DA::FL_USR_USER.' = '.$this->format_string_to_sql_param($username);
//        echo '<br>user_DA->update_user_last_login : '.$sql_cmd;
        $this->execute_sql_command($sql_cmd, false);
    }

    /**
     * (PHP 5)<br/>
     * Create a session token in DB
     * @param int $user_id
     * @param string $token
     */
    public function create_token($user_id, $token){
        $sql_cmd = 'INSERT INTO '.$this->get_table_prefix().'_user_token ('.user_DA::FL_TOK_USER_ID.', '.user_DA::FL_TOK_TOKEN.', '.user_DA::FL_TOK_TOKEN_EXPIRED.'
                        ) VALUES ('.$user_id.', '.$this->format_string_to_sql_param($token).', DATE_ADD(NOW(), INTERVAL 1 HOUR))';
//        echo '<br>user_DA->create_token : '.$sql_cmd;
        $this->execute_sql_command($sql_cmd, false);
    }

    /**
     * (PHP 5)<br/>
     * Suppress a session token 
     * @param int $user_id
     * @param string $token
     */
    public function suppress_token($user_id, $token){
        $sql_cmd = 'DELETE FROM '.$this->get_table_prefix().'_user_token WHERE '.user_DA::FL_TOK_USER_ID.' = '.$user_id
                            .' AND '.user_DA::FL_TOK_TOKEN.' = '.$this->format_string_to_sql_param($token);
//        echo '<br>user_DA->suppress_token : '.$sql_cmd;
        $this->execute_sql_command($sql_cmd, false);
    }

    /**
     * (PHP 5)<br/>
     * Clean expired session token  in DB
     */
    public function suppress_expired_token(){
        $sql_cmd = 'DELETE FROM '.$this->get_table_prefix().'_user_token WHERE '.user_DA::FL_TOK_TOKEN_EXPIRED.' < NOW()';
//        echo '<br>user_DA->suppress_expired_token : '.$sql_cmd;
        $this->execute_sql_command($sql_cmd, false);
    }

    /**
     * (PHP 5)<br/>
     * Check if the tokn is valid
     * @param int $user_id
     * @param string $token
     * @return bool
     */
    public function is_valid_token($user_id, $token){
        $sql_cmd = 'SELECT '.user_DA::FL_TOK_USER_ID.' FROM '.$this->get_table_prefix().'_user_token WHERE '.user_DA::FL_TOK_USER_ID.' = '.$user_id
                            .' AND '.user_DA::FL_TOK_TOKEN.' = '.$this->format_string_to_sql_param($token)
                            .' AND '.user_DA::FL_TOK_TOKEN_EXPIRED.' > NOW()';
//        echo '<br>user_DA->is_valid_token : '.$sql_cmd;
        $result = $this->execute_sql_command($sql_cmd, false);
        return !empty($result);
    }

    /**
     * (PHP 5)<br/>
     * Refresh the expiration date of the current session token
     * @param int $user_id
     * @param string $token
     */
    public function refresh_token_expiration_date($user_id, $token){
        $sql_cmd = 'UPDATE '.$this->get_table_prefix().'_user_token SET '.user_DA::FL_TOK_TOKEN_EXPIRED.' = DATE_ADD(NOW(), INTERVAL 1 HOUR) WHERE '.user_DA::FL_TOK_USER_ID.' = '.$user_id
                            .' AND '.user_DA::FL_TOK_TOKEN.' = '.$this->format_string_to_sql_param($token);
//        echo '<br>user_DA->refresh_token_expiration_date : '.$sql_cmd;
        $this->execute_sql_command($sql_cmd, false);
    }

    /**
     * (PHP 5)<br/>
     * Check if a user is banned
     * @param string $username
     * @return bool
     */
    public function is_user_banned($username){
        $sql_cmd = 'SELECT * FROM '.$this->get_table_prefix().'_user WHERE '.user_DA::FL_USR_DEBAN_DATE.' > NOW() AND '.user_DA::FL_USR_ID.' = '.$this->format_string_to_sql_param($username);
//        echo '<br>user_DA->is_user_banned : '.$sql_cmd;
        $result = $this->execute_sql_command($sql_cmd, false);
        return !empty($result);
    }

    /**
     * (PHP 5)<br/>
     * Get the daban date of a user
     * @param string $username
     * @return datetime
     */
    public function get_deban_date($username){
        $sql_cmd = 'SELECT '.user_DA::FL_USR_DEBAN_DATE.' FROM '.$this->get_table_prefix().'_user WHERE '.user_DA::FL_USR_USER.' = '.$this->format_string_to_sql_param($username);
//        echo '<br>user_DA->get_deban_date : '.$sql_cmd;
        $result = $this->execute_sql_command($sql_cmd, false);
        return isset($result) ? $result[0][0] : null;
    }

    /**
     * (PHP 5)<br/>
     * Add a failed user logging attempt in DB
     * @param string $username
     * @param int $reset_failed_logging_count_time in minutes
     */
    public function add_failed_logging_attempt($username, $reset_failed_logging_count_time){
        $sql_cmd = 'UPDATE '.$this->get_table_prefix().'_user SET '.user_DA::FL_USR_LAST_LOGIN_FAILED_DATE.' = NOW(), '.user_DA::FL_USR_LOGIN_FAILED_COUNT.' = CASE WHEN DATE_ADD('.user_DA::FL_USR_LAST_LOGIN_FAILED_DATE.', INTERVAL '
                        .$reset_failed_logging_count_time.' MINUTE ) > NOW() THEN '.user_DA::FL_USR_LOGIN_FAILED_COUNT.' + 1 ELSE 1 END '
                        .' WHERE '.user_DA::FL_USR_USER.' = '.$this->format_string_to_sql_param($username);

//        echo '<br>user_DA->add_failed_logging_attempt : '.$sql_cmd;
        $this->execute_sql_command($sql_cmd, false);
    }

    /**
     * (PHP 5)<br/>
     * Get a failed user logging attempt count
     * @param string $username
     * @param int $reset_failed_logging_count_time in minutes
     * @return <int
     */
    public function get_failed_logging_attempt_count($username, $reset_failed_logging_count_time){
        $sql_cmd = 'SELECT CASE WHEN DATE_ADD('.user_DA::FL_USR_LAST_LOGIN_FAILED_DATE.', INTERVAL '
                        .$reset_failed_logging_count_time.' MINUTE ) > NOW() THEN '.user_DA::FL_USR_LOGIN_FAILED_COUNT.' ELSE 0 END AS login_failed_count '
                        .' FROM '.$this->get_table_prefix().'_user WHERE user = '.$this->format_string_to_sql_param($username);
//        echo '<br>user_DA->get_failed_logging_attempt_count : '.$sql_cmd;
        $result = $this->execute_sql_command($sql_cmd, false);
        return isset($result) ? $result[0][0] : 0;
    }

    /**
     * (PHP 5)<br/>
     * Ban a user for X minutes
     * @param string $username
     * @param int $time_of_ban in minutes
     */
    public function ban_user($username, $time_of_ban){
        $sql_cmd = 'UPDATE '.$this->get_table_prefix().'_user SET '.user_DA::FL_USR_DEBAN_DATE.' = DATE_ADD(NOW(), INTERVAL '.$time_of_ban.' MINUTE) WHERE '.user_DA::FL_USR_ID.' = '.$this->format_string_to_sql_param($username);
//        echo '<br>user_DA->ban_user : '.$sql_cmd;
        $this->execute_sql_command($sql_cmd, false);
    }

    /**
     * (PHP 5)<br/>
     * Update the group of a user
     * @param int $user_id
     * @param int $group_id
     */
    public function update_user_group($user_id, $group_id){
        $sql_cmd = 'UPDATE '.$this->get_table_prefix().'_user SET '.user_DA::FL_USR_GROUP_ID.' = '.$group_id.' WHERE '.user_DA::FL_USR_ID.' = '.$user_id;
//        echo '<br>user_DA->update_user_group : '.$sql_cmd;
        $this->execute_sql_command($sql_cmd, false);
    }

    /**
     * (PHP 5)<br/>
     * Get the user group id
     * @param int $user_id
     * @return int
     */
    public function get_user_group($user_id){
        $sql_cmd = 'SELECT '.user_DA::FL_USR_GROUP_ID.' FROM '.$this->get_table_prefix().'_user WHERE '.user_DA::FL_USR_ID.' = '.$user_id;
//        echo '<br>user_DA->get_user_group : '.$sql_cmd;
        $result = $this->execute_sql_command($sql_cmd, false);
        return isset($result) ? $result[0][0] : 0;
    }

    /**
     * (PHP 5)<br/>
     * Get user list
     * @param $group_id
     * @return array of User
     */
    public function get_user_list($group_id){
        $result = array();
        $sql_cmd = 'SELECT * FROM '.$this->get_table_prefix().'_user ';

        if (isset($group_id)){
            $sql_cmd .= ' WHERE '.user_DA::FL_USR_GROUP_ID.' = '.$group_id.' ';
        }

        $sql_cmd .= ' ORDER BY '.user_DA::FL_USR_USER.'';
//        echo '<br>user_DA->get_user_list : '.$sql_cmd;
        $user_list = $this->execute_sql_command($sql_cmd, false);

        if (isset($user_list)){
            foreach($user_list as $user){
                $result[] = new User($user[user_DA::FL_USR_ID], $user[user_DA::FL_USR_USER], $user[user_DA::FL_USR_EMAIL], $user[user_DA::FL_USR_CREATION_DATE]
                        ,$user[user_DA::FL_USR_LOGIN_FAILED_COUNT], $user[user_DA::FL_USR_LAST_LOGIN_FAILED_DATE], $user[user_DA::FL_USR_ACTIVE], $user[user_DA::FL_USR_ACTICATION_TOKEN]
                        ,$user[user_DA::FL_USR_DEBAN_DATE], $user[user_DA::FL_USR_GROUP_ID], $user[user_DA::FL_USR_LAST_LOGIN]);
            }
        }

        return (isset($result) ? $result : null);
    }

    /**
     * (PHP 5)<br/>
     * Get a user in DB
     * @param int $id
     * @return User
     */
    public function get_user($id){
        $sql_cmd = 'SELECT * FROM '.$this->get_table_prefix().'_user WHERE '.user_DA::FL_USR_ID.' = '.$id;
//        echo '<br>user_DA->get_user : '.$sql_cmd;
        $u = $this->execute_sql_command($sql_cmd, false);

        if ((isset($u)) && (isset($u[0]))){
            $user = $u[0];

            $result = new User($user[user_DA::FL_USR_ID], $user[user_DA::FL_USR_USER], $user[user_DA::FL_USR_EMAIL], $user[user_DA::FL_USR_CREATION_DATE]
                        ,$user[user_DA::FL_USR_LOGIN_FAILED_COUNT], $user[user_DA::FL_USR_LAST_LOGIN_FAILED_DATE], $user[user_DA::FL_USR_ACTIVE], $user[user_DA::FL_USR_ACTICATION_TOKEN]
                        , $user[user_DA::FL_USR_DEBAN_DATE], $user[user_DA::FL_USR_GROUP_ID], $user[user_DA::FL_USR_LAST_LOGIN]);
        }

        return (isset($result) ? $result : null);
    }

    /**
     * (PHP 5)<br/>
     * Get a user id with username or email
     * @param string $user_name_or_email
     * @return int
     */
    public function get_user_id_by_name_or_email($user_name_or_email){
        $sql_cmd = 'SELECT '.user_DA::FL_USR_ID.' FROM '.$this->get_table_prefix().'_user WHERE '.user_DA::FL_USR_USER.' = '
                    .$this->format_string_to_sql_param($user_name_or_email).' OR '.user_DA::FL_USR_EMAIL.' = '.$this->format_string_to_sql_param($user_name_or_email);
//        echo '<br>user_DA->get_user_id_by_name_or_email : '.$sql_cmd;
        $id = $this->execute_sql_command($sql_cmd, false);

        return (isset($id) ? $id[0][0] : null);
    }

    /**
     * (PHP 5)<br/>
     * Get user group list
     * @return array of  User_Group
     */
    public function get_user_group_list(){
        $result = array();
        $sql_cmd = 'SELECT * FROM '.$this->get_table_prefix().'_user_group';
//        echo '<br>user_DA->get_user_group : '.$sql_cmd;
        $group_list = $this->execute_sql_command($sql_cmd, false);

        if (isset($group_list)){
            foreach ($group_list as $group){
                $result[] = new User_Group($group[user_DA::FL_GRP_ID], $group[user_DA::FL_GRP_LABEL]);
            }
        }

        return (isset($result) ? $result : null);
    }

    /**
     * (PHP 5)<br/>
     * Change user password
     * @param int $user_id
     * @param string $new_password
     */
    public function change_password($user_id, $new_password){
        $sql_cmd = 'UPDATE '.$this->get_table_prefix().'_user SET '.user_DA::FL_USR_PASSWORD.' = '.$this->format_string_to_sql_param($new_password)
                        .' WHERE '.user_DA::FL_USR_ID.' = '.$user_id;
//        echo '<br>user_DA->change_password : '.$sql_cmd;
        $this->execute_sql_command($sql_cmd, false);
    }

}



?>
